package de.dhbwstuttgart.vincon.telefoncompany.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import de.dhbwstuttgart.vincon.telefoncompany.model.Call;
import de.dhbwstuttgart.vincon.telefoncompany.model.Customer;

public class DataCreatorH2 {

	private static final int MONTH_QUANTITY = 12;
	private static final int CALLEE_QUANTITY = 1;
	private static final int CALL_QUANTITY = 3;
	private static final int CUSTOMER_QUANTITY = 100;

	private TextReader forename;
	private TextReader surname;
	private TextReader dailingcode;

	public DataCreatorH2() {
		forename = new TextReader("forename.txt");
		surname = new TextReader("surname.txt");
		dailingcode = new TextReader("dailingcode.txt");
	}

	public void createData(int factor, Connection conn) {
		try {
			Statement stat = conn.createStatement();
			Statement statCustomer = conn.createStatement();
			Statement statCallee = conn.createStatement();
			Statement statCaller = conn.createStatement();
			Statement statCallInsert = conn.createStatement();

			StringBuilder customerInsert = new StringBuilder(
					"INSERT INTO customer (forename, surname) VALUES ");
			StringBuilder phoneInsert = new StringBuilder(
					"INSERT INTO phone (number, customerNr) VALUES ");
			StringBuilder callInsert;
			int specialCustomer = 0;
			Customer customer;
			Timestamp start;
			Timestamp end;
			Call call;
			int phoneQuantity = 0;

			// create customers
			for (int i = 0; i < CUSTOMER_QUANTITY * factor; i++) {
				if (specialCustomer == 0) {
					specialCustomer = (int) (Math.random() * 10);

					if (specialCustomer == 10
							|| i == CUSTOMER_QUANTITY * factor - 1) {
						customer = addSpecialCustomer();
					} else {
						customer = addCustomer();
						specialCustomer = 0;
					}
				} else {
					customer = addCustomer();
				}

				customerInsert.append("('" + customer.getForename() + "', '"
						+ customer.getSurname() + "'), ");
				
				if((i+1)%1001 == 0){
					customerInsert.delete(customerInsert.length() - 2,
							customerInsert.length());
					stat.executeUpdate(customerInsert.toString());
					
					customerInsert = new StringBuilder(
							"INSERT INTO customer (forename, surname) VALUES ");
				}
			}

			customerInsert.delete(customerInsert.length() - 2,
					customerInsert.length());
			stat.executeUpdate(customerInsert.toString());

			ResultSet customers = statCustomer
					.executeQuery("SELECT id FROM customer");

			int i = 0;
			// create phones for each customer
			while (customers.next()) {
				phoneInsert.append("('" + getPhoneNumber() + "', "
						+ customers.getInt(1) + "), ");

				phoneQuantity++;
				if (phoneQuantity == 2) {
					phoneInsert.append("('" + getPhoneNumber() + "', "
							+ customers.getInt(1) + "), ");
					phoneQuantity = 0;
				}
				
				if((i+1)%1001 == 0){
					phoneInsert.delete(phoneInsert.length() - 2, phoneInsert.length());
					stat.executeUpdate(phoneInsert.toString());
					
					phoneInsert = new StringBuilder(
							"INSERT INTO phone (number, customerNr) VALUES ");
				}				
				i++;
			}

			phoneInsert.delete(phoneInsert.length() - 2, phoneInsert.length());
			stat.executeUpdate(phoneInsert.toString());

			// create calls for each month
			for (int month = 0; month < MONTH_QUANTITY; month++) {

				customers = stat.executeQuery("SELECT id FROM customer");

				callInsert = new StringBuilder(
						"INSERT INTO call (caller, callee, start, end) VALUES ");

				while (customers.next()) {

					ArrayList<Integer> callee = resultSetToArrayList(statCallee
							.executeQuery("SELECT id FROM phone ORDER BY RAND () LIMIT "
									+ CALLEE_QUANTITY * factor));
					ArrayList<Integer> caller = resultSetToArrayList(statCaller
							.executeQuery("SELECT id FROM phone WHERE customerNr = "
									+ customers.getInt(1)));
					call = createCall(month);
					start = new Timestamp(call.getStart());
					end = new Timestamp(call.getEnd());
					
					callInsert = new StringBuilder(
							"INSERT INTO call (caller, callee, start, end) VALUES ");

					for (int calls = 0; calls < CALL_QUANTITY * factor; calls++) {
						callInsert.append("(" + getRandomId(caller) + ", "
								+ getRandomId(callee) + ", '"
								+ start.toString() + "', '" + end.toString()
								+ "'), ");
					}
					
					callInsert.delete(callInsert.length() - 2, callInsert.length());
					statCallInsert.executeUpdate(callInsert.toString());

				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private Customer addCustomer() {
		Customer customer = new Customer(forename.getRandomData(),
				surname.getRandomData());
		return customer;
	}

	private Customer addSpecialCustomer() {
		Customer customer = new Customer("Max", "Mustermann");
		return customer;
	}

	private String getPhoneNumber() {
		String number = dailingcode.getRandomData() + "/"
				+ (int) (Math.random() * 999999 + 1000);
		return number;
	}

	private ArrayList<Integer> resultSetToArrayList(ResultSet resultSet) {
		ArrayList<Integer> results = new ArrayList<Integer>();

		try {
			while (resultSet.next()) {
				results.add(resultSet.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return results;
	}

	private int getRandomId(ArrayList<Integer> results) {
		return results.get((int) (Math.random() * results.size()));
	}

	private Call createCall(int month) {
		int year = 2012;
		int dayOfMonth = (int) (Math.random() * 25);

		int hourOfDay1 = (int) (Math.random() * 24);
		int minute1 = (int) (Math.random() * 60);
		int second1 = (int) (Math.random() * 60);

		int hourOfDay2 = (int) (Math.random() * 24);
		int minute2 = (int) (Math.random() * 60);
		int second2 = (int) (Math.random() * 60);

		Calendar time1 = new GregorianCalendar();
		time1.set(Calendar.YEAR, year);
		time1.set(Calendar.MONTH, month);
		time1.set(Calendar.DAY_OF_MONTH, dayOfMonth);
		time1.set(Calendar.HOUR_OF_DAY, hourOfDay1);
		time1.set(Calendar.MINUTE, minute1);
		time1.set(Calendar.MINUTE, second1);
		Calendar time2 = new GregorianCalendar();
		time2.set(Calendar.YEAR, year);
		time2.set(Calendar.MONTH, month);
		time2.set(Calendar.DAY_OF_MONTH, dayOfMonth);
		time2.set(Calendar.HOUR_OF_DAY, hourOfDay2);
		time2.set(Calendar.MINUTE, minute2);
		time2.set(Calendar.MINUTE, second2);

		Call call;

		if (time1.before(time2)) {
			call = new Call(time1, time2, null, null);
		} else {
			call = new Call(time2, time1, null, null);
		}

		return call;
	}
}